SELECT DISTINCT ND.NODE_NAME, ND.NODE_ID 
FROM C_PRD_NODES_PARENT NP 
LEFT JOIN C_PRD_NODES ND ON NP.PARENT_ID = ND.NODE_ID 
WHERE NP.NODE_TYPE = 'ROOT'

SELECT ND.NODE_NAME, ND.NODE_ID FROM C_PRD_NODES ND
WHERE NODE_ID IN (
SELECT NODE_ID FROM C_PRD_NODES_PARENT NP )

SELECT *
FROM C_PRD_NODES_PARENT NP 

SELECT * FROM X_NODE_TYPE_ATTR 
WHERE NODE_TYPE_ID = (
SELECT NODE_TYPE_ID FROM C_PRD_NODES WHERE NODE_ID = 2)

 SELECT ATTR_NAME FROM X_NODE_TYPE_ATTR WHERE NODE_TYPE_ID = ( SELECT NODE_TYPE_ID FROM C_PRD_NODES WHERE NODE_ID = 2) 